/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.situ.studentmanagement.service;

import com.situ.studentmanagement.database.DBHelper;
import com.situ.studentmanagement.model.ClassRoom;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 *
 * @author niugao
 */
public class ClassManService {

    //添加一个班级，返回的是添加的班级的ID
    public int addClass(ClassRoom cls) throws ClassNotFoundException, SQLException {
        int id = 0;
        String sql = "insert into " + ClassRoom.TABLE_NAME + " ("
                + ClassRoom.NAME + "," + ClassRoom.LEVEL + ","
                + ClassRoom.SCHOOL_NAME + "," + ClassRoom.ADMIN_ID
                + ") values (?,?,?,?)";

        Connection conn = DBHelper.getConnection();
        //准备一个SQL语句对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        Object[] objs = {cls.getName(), cls.getLevel(), cls.getSchool_name(), cls.getAdmin_id()};
        for (int i = 0; i < objs.length; i++) {
            pstmt.setObject(i + 1, objs[i]);
        }
        pstmt.executeUpdate();

        //FIXME:获取插入这行的ID
        cls.setId(id);
        
        conn.close();
        pstmt.close();
        return id;
    }

    public void deleteClass(String id) throws ClassNotFoundException, SQLException {
        String sql = "delete from " + ClassRoom.TABLE_NAME + " where id=" + id;

        Connection conn = DBHelper.getConnection();
        //准备一个SQL语句对象
        Statement pstmt = conn.createStatement();
        pstmt.executeUpdate(sql);

        conn.close();
        pstmt.close();
    }

    public void modifyClass(ClassRoom cls)
            throws ClassNotFoundException, SQLException {
        String sql = "update " + ClassRoom.TABLE_NAME + " set "
                + ClassRoom.NAME + "=?,"
                + ClassRoom.LEVEL + "=?,"
                + ClassRoom.SCHOOL_NAME + "=?,"
                + ClassRoom.ADMIN_ID + "=? "
                + "where " + ClassRoom.ID + "=?";

        Connection conn = DBHelper.getConnection();
        //准备一个SQL语句对象
        PreparedStatement pstmt = conn.prepareStatement(sql);
        Object[] objs = {cls.getName(), cls.getLevel(), cls.getSchool_name(), cls.getAdmin_id(), cls.getId()};
        for (int i = 0; i < objs.length; i++) {
            pstmt.setObject(i + 1, objs[i]);
        }
        pstmt.executeUpdate();

        conn.close();
        pstmt.close();
    }

    public ClassRoom findClass(String id) throws ClassNotFoundException, SQLException {
        String sql = "select * from " + ClassRoom.TABLE_NAME;
        Connection conn = DBHelper.getConnection();
        //准备一个SQL语句对象
        Statement pstmt = conn.createStatement();
        ResultSet rs = pstmt.executeQuery(sql);
        //跟据结果，创建一个ClassRoom实例
        ClassRoom cls = null; 
        if (rs.next()) {
            cls=new ClassRoom(rs.getInt(ClassRoom.ID),
                    rs.getString(ClassRoom.NAME),
                    rs.getString(ClassRoom.LEVEL),
                    rs.getString(ClassRoom.SCHOOL_NAME),
                    rs.getInt(ClassRoom.ADMIN_ID));
        }
        return cls;
    }
}
